package com.xueyi.aixihu.mapper;

import com.xueyi.aixihu.entity.vo.AxhFinanceVo;
import com.xueyi.aixihu.entity.vo.FinanceExpenseSizeVo;
import com.xueyi.aixihu.entity.vo.FindWaitAuditReportFormListVo;
import com.xueyi.aixihu.entity.vo.FindWaitFinanceSizeVo;
import com.xueyi.aixihu.reqeust.FindTownWaitAuditFinanceListReq;
import com.xueyi.aixihu.reqeust.FindWaitAuditReportFormListReq;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import javax.validation.constraints.NotNull;
import java.util.List;

/**
 * @author hxs
 * @createDate 2023/6/29 11:08
 * @description 财务三资审核接口
 */
public interface ReportFormsAuditMapper {
    /**
     * 查询待审核的报表
     *
     * @param findWaitAuditReportFormListReq 参数
     * @param areaIds                        地区
     * @return 列表
     */
    @Select({
            " <script> " +
                    " select af.* , tab.id auditId , du.username  , dns.name areaName from (   " +
                    "   select * from cdz_audit_record " +
                    " where is_valid = 1 " +
                    " <if test = 'item.currentStatus != null'>" +
                    "  and current_status in ( ${item.currentStatusStr} ) " +
                    " </if> " +
                    " <if test = 'item.reportType != null'>" +
                    "  and audit_type = #{item.reportType} " +
                    " </if> " +
                    ") tab left join axh_finance af on tab.join_id = af.id    " +
                    "left join dt_users du on af.create_user_id = du.id   " +
                    "left join dt_news_sort dns on af.from_to_area_id = dns.id   " +
                    "where af.from_to_area_id in (${areaIds}) " +
                    " <if test = 'item.keyword != null'>" +
                    " and af.finance_title like '%${item.keyword}%' " +
                    " </if> " +
                    " <if test = 'item.month != null'>" +
                    "  and DATE_FORMAT(af.create_time,'%Y-%m') = #{item.month} " +
                    " </if> " +
                    " <if test = 'item.oneIndex != null'>" +
                    "  and af.area_type = #{item.oneIndex} " +
                    " </if> " +
                    " <if test = 'item.twoIndex != null'>" +
                    "  and af.from_to_area_id = #{item.twoIndex} " +
                    " </if> " +
                    "  order by tab.submit_time " +
                    " limit #{item.startIndex} , #{item.limit} " +
                    " </script> "
    })
    List<FindWaitAuditReportFormListVo> findWaitAuditReportFormList(@Param("item") FindWaitAuditReportFormListReq findWaitAuditReportFormListReq, @Param("areaIds") String areaIds);


    /**
     * 查询待审核报表总条数
     *
     * @param findWaitAuditReportFormListReq 参数
     * @param areaIds                        地区id
     * @return 总条数
     */
    @Select({
            " <script> " +
                    " select count(af.id) from (   " +
                    "   select * from cdz_audit_record " +
                    " where is_valid = 1 " +
                    " <if test = 'item.currentStatus != null'>" +
                    "  and current_status in ( ${item.currentStatusStr} )  " +
                    " </if> " +
                    " <if test = 'item.reportType != null'>" +
                    "  and audit_type = #{item.reportType} " +
                    " </if> " +
                    ") tab left join axh_finance af on tab.join_id = af.id    " +
//                    "left join dt_users du on af.create_user_id = du.id   " +
//                    "left join dt_news_sort dns on af.from_to_area_id = dns.id   " +
                    "where af.from_to_area_id in (${areaIds})  " +
                    " <if test = 'item.keyword != null'>" +
                    " and af.finance_title like '%${item.keyword}%' " +
                    " </if> " +
                    " <if test = 'item.month != null'>" +
                    "  and DATE_FORMAT(af.create_time,'%Y-%m') = #{item.month} " +
                    " </if> " +
                    " <if test = 'item.oneIndex != null'>" +
                    "  and af.area_type = #{item.oneIndex} " +
                    " </if> " +
                    " <if test = 'item.twoIndex != null'>" +
                    "  and af.from_to_area_id = #{item.twoIndex} " +
                    " </if> " +
                    " </script> "
    })
    Integer findWaitAuditReportFormListTotal(@Param("item") FindWaitAuditReportFormListReq findWaitAuditReportFormListReq, @Param("areaIds") String areaIds);

    /**
     * 查询镇级待审核财务报表的条数
     *
     * @param findTownWaitAuditFinanceListReq 参数
     * @return 列表
     */
    @Select(" <script> " +
            " SELECT COUNT(af.id) FROM ( " +
            " SELECT * FROM cdz_audit_record " +
            " WHERE is_valid = 1 " +
            " <if test = 'req.currentStatus != null and req.currentStatus == 4'>" +
            "  AND current_status IN (0,1) " +
            " </if> " +
            " <if test = 'req.currentStatus != null and req.currentStatus != 4'>" +
            "  AND current_status = #{req.currentStatus} " +
            " </if> " +
            " <if test = 'req.reportType != null'>" +
            "  AND audit_type = #{req.reportType} " +
            " </if> " +
            " ) tab LEFT JOIN axh_finance af ON tab.join_id = af.id " +
            " LEFT JOIN dt_users du on af.create_user_id = du.id " +
            " LEFT JOIN dt_news_sort dns ON af.from_to_area_id = dns.id " +
            " WHERE af.from_to_area_id IN (${areaIds}) AND af.area_type = #{zj} " +
            " <if test = 'req.time != null'>" +
            "  AND af.create_time LIKE '%${req.time}%' " +
            " </if> " +
            " <if test = 'req.keyword != null'>" +
            "  AND af.finance_title LIKE '%${req.keyword}%' " +
            " </if> " +
            " </script> ")
    Integer findTownWaitAuditFinanceListTotal(@Param("req") FindTownWaitAuditFinanceListReq findTownWaitAuditFinanceListReq,
                                              @Param("areaIds") String areaIds,
                                              @Param("zj") Integer zj);

    /**
     * 查询镇级待审核财务报表的数据
     *
     * @param findTownWaitAuditFinanceListReq 参数
     * @return 列表
     */
    @Select(" <script> " +
            " SELECT af.* , tab.id auditId  , dns.name areaName, du.avatar photo, du.username username  FROM ( " +
            " SELECT * FROM cdz_audit_record " +
            " WHERE is_valid = 1 " +
            " <if test = 'req.currentStatus != null and req.currentStatus == 4'>" +
            "  AND current_status IN (0,1) " +
            " </if> " +
            " <if test = 'req.currentStatus != null and req.currentStatus != 4'>" +
            "  AND current_status = #{req.currentStatus} " +
            " </if> " +
            " <if test = 'req.reportType != null'>" +
            "  AND audit_type = #{req.reportType} " +
            " </if> " +
            " ) tab LEFT JOIN axh_finance af ON tab.join_id = af.id " +
            " LEFT JOIN dt_users du on af.create_user_id = du.id " +
            " LEFT JOIN dt_news_sort dns ON af.from_to_area_id = dns.id " +
            " WHERE af.from_to_area_id IN (${areaIds}) AND af.area_type = #{zj} " +
            " <if test = 'req.time != null'>" +
            "  AND af.create_time LIKE '%${req.time}%' " +
            " </if> " +
            " <if test = 'req.keyword != null'>" +
            "  AND af.finance_title LIKE '%${req.keyword}%' " +
            " </if> " +
            " ORDER BY af.create_time " +
            " LIMIT #{req.startIndex} , #{req.limit} " +
            " </script> ")
    List<FindWaitAuditReportFormListVo> findTownWaitAuditFinanceList(@Param("req") FindTownWaitAuditFinanceListReq findTownWaitAuditFinanceListReq,
                                                                     @Param("areaIds") String areaIds,
                                                                     @Param("zj") Integer zj);

    /**
     * 查询不同状态的条数
     *
     * @param findTownWaitAuditFinanceListReq
     * @param areaIds
     * @return
     */
    @Select("<script>" +
            " SELECT " +
            " COUNT(IF(current_status IN (1,0) AND (fb_reject_detail IS NULL) , id , null)) totalSize , " +
            " COUNT(IF(current_status = 1 AND (fb_reject_detail IS NULL) , id , null)) passSize , " +
            " COUNT(IF(current_status = 0 AND (fb_reject_detail IS NULL) , id , null)) waitSize " +
            " FROM axh_finance WHERE is_valid = 1 AND from_to_area_id IN (${areaIds}) AND area_type = #{zj} " +
            " <if test='req.keyword != null'>" +
            "  AND finance_title LIKE '%${req.keyword}%' " +
            " </if> " +
            " <if test = 'req.time != null'>" +
            "  AND create_time LIKE '%${req.time}%' " +
            " </if> " +
            " <if test = 'req.reportType != null'>" +
            "  AND report_type = #{req.reportType} " +
            " </if> " +
            "</script>")
    FinanceExpenseSizeVo findFinanceAuditTypeSize(@Param("req") FindTownWaitAuditFinanceListReq findTownWaitAuditFinanceListReq,
                                                  @Param("areaIds") String areaIds,
                                                  @Param("zj") Integer zj);

    @Select(" <script> " +
            " SELECT af.* , tab.id auditId  , dns.name areaName  FROM ( " +
            " SELECT * FROM cdz_audit_record " +
            " WHERE is_valid = 1 " +
            " <if test = 'req.currentStatus != null'>" +
            "  AND current_status = #{req.currentStatus} " +
            " </if> " +
            " <if test = 'req.reportType != null'>" +
            "  AND audit_type = #{req.reportType} " +
            " </if> " +
            " ) tab LEFT JOIN axh_finance af ON tab.join_id = af.id " +
            " LEFT JOIN dt_news_sort dns ON af.from_to_area_id = dns.id " +
            " WHERE af.from_to_area_id IN (${areaIds}) " +
            " <if test = 'req.time != null'>" +
            "  AND tab.submit_time LIKE '%${req.time}%' " +
            " </if> " +
            " <if test = 'req.keyword != null'>" +
            "  AND af.finance_title LIKE '%${req.keyword}%' " +
            " </if> " +
            " ORDER BY tab.submit_time " +
            " LIMIT #{req.startIndex} , #{req.limit} " +
            " </script> ")
    List<FindWaitAuditReportFormListVo> findAreaFinanceList(@Param("req") FindWaitAuditReportFormListReq findWaitAuditReportFormListReq,
                                                            @Param("areaIds") String areaIds);

    /**
     * 查询镇级财务报表页面数据
     *
     * @return
     */
    @Select({
            " select   " +
                    "  COUNT(IF(current_status in (${waitAuditSizeStr}),id,null)) waitAuditSize,  " +
                    "  COUNT(IF(current_status in (${agreeAuditSizeStr}),id,null)) agreeAuditSize,  " +
                    "  COUNT(IF(current_status in (${refuseAuditSize}),id,null)) refuseAuditSize " +
                    " from axh_finance where is_valid = 1 and report_type = #{reportType} " +
                    " and area_type in (${address}) and from_to_area_id in (${areaIds}) "
    })
    FindWaitFinanceSizeVo findWaitFinanceSize(@Param("areaIds") String areaIds, @Param("address") String address,
                                              @Param("reportType") Integer reportType, @Param("waitAuditSizeStr") String waitAuditSizeStr,
                                              @Param("agreeAuditSizeStr") String agreeAuditSizeStr, @Param("refuseAuditSize") String refuseAuditSize);

    /**
     * 通过报表id查询报表
     *
     * @param id 报表id
     * @return 报表
     */
    @Select({
            " select tab.* , car.fail_msg from (" +
                    " select * from axh_finance where id = #{id} and is_valid = 1" +
                    " ) tab left join cdz_audit_record car on tab.id = car.join_id order by submit_time desc limit 1 "
    })
    AxhFinanceVo findFinanceById(@Param("id") Integer id);

    /**
     * 修改报表信息
     *
     * @param flag 参数
     */
    @Update({
            " update axh_finance set finance_title = #{item.financeTitle}  , create_time = #{item.createTimeStr} ,last_update_time = now() " +
                    " , system_type_id = #{item.systemTypeId} where id = #{item.id}"
    })
    void updateReportFormInfo(@Param("item") AxhFinanceVo flag);

    /**
     * 转为草稿
     *
     * @param id    报表id
     * @param draft 状态
     */
    @Update({
            " update axh_finance set current_status = #{draft} where id = #{id} "
    })
    void transferDraft(@Param("id") Integer id, @Param("draft") Integer draft);
}
